package no.weather.weatherProvider;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import no.weather.WeatherStation;
import no.weather.WeatherType;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;

/**
 * @author torstein
 * 
 */
public class WeatherDatabase {
	private static final String DATABASE_NAME = "weather";
	private static final int DATABASE_VERSION = 14;
	public static final String ELEMENT_TO_STATION_ELEMENT = "symbol";
	public static final String ELEMENT_TO_STATION_STATION_ID = "station";
	public static final String ELEMENT_TO_STATION_STATION_PROVIDER = "provider";
	public static final String ELEMENT_TO_STATION_TABLE_NAME = "elements";
	public static final String STATION_ALTITUDE = "altitude";
	public static final String STATION_DELIVERS_FROM = "deliversFrom";
	public static final String STATION_DELIVERS_TO = "deliversTo";
	public static final String STATION_ELEMENT_IS_VALID = "elementsIsValid";
	public static final String STATION_ID = "id";
	public static final String STATION_IS_FAVORITE = "isFavorite";
	public static final String STATION_LATITUDE = "latitude";
	public static final String STATION_LONGTITUDE = "longtitude";
	public static final String STATION_NAME = "name";
	public static final String STATION_PROVIDER = "provider";
	public static final String STATION_REFRESS_RATE = "refressRate";
	public static final String STATION_TABLE_NAME = "station";

	private final WeatherDatabaseOpenHelper databaseOpenHelper;

	public WeatherDatabase(Context context) {
		databaseOpenHelper = new WeatherDatabaseOpenHelper(context);
	}

	public void add(WeatherStation station) throws StationIsDeletedException {
		ContentValues value = new ContentValues();
		value.put(STATION_ID, station.getId());
		value.put(STATION_NAME, station.getName());
		value.put(STATION_LATITUDE, station.getLocation().getLatitude());
		value.put(STATION_LONGTITUDE, station.getLocation().getLongitude());
		value.put(STATION_ALTITUDE, station.getLocation().getAltitude());
		value.put(STATION_DELIVERS_FROM, station.getDeliverFrom().getTime());
		value.put(STATION_DELIVERS_TO, station.getDeliverTo().getTime());
		value.put(STATION_REFRESS_RATE, station.getRefressRate());
		value.put(STATION_PROVIDER, station.getProvider());

		if (!station.getTypesIsKnown()) {
			value.put(STATION_ELEMENT_IS_VALID, false);
		} else {
			value.put(STATION_ELEMENT_IS_VALID, true);
			setTypesToStation(station.getTypes(), station.getId(),
					station.getProvider());
		}

		databaseOpenHelper.getWritableDatabase().insertOrThrow(
				STATION_TABLE_NAME, null, value);

	}

	public void addFavorite(String stationId, String stationProvider) {
		setIsFavorite(stationId, stationProvider, true);

	}

	public void addTypeToStation(String element, String stationId,
			String stationProvider, Boolean setTypeIsKnown) {
		ContentValues value = new ContentValues();
		value.put(ELEMENT_TO_STATION_STATION_ID, stationId);
		value.put(ELEMENT_TO_STATION_STATION_PROVIDER, stationProvider);
		value.put(ELEMENT_TO_STATION_ELEMENT, element);
		databaseOpenHelper.getWritableDatabase().insert(
				ELEMENT_TO_STATION_TABLE_NAME, null, value);
		if (setTypeIsKnown) {
			setTypeIsKnown(true, stationId, stationProvider);
		}
	}

	public void close() {
		databaseOpenHelper.close();
	}

	public void deleteFavorite(String stationId, String stationProvider) {
		setIsFavorite(stationId, stationProvider, false);
	}

	/**
	 * Deletes a station from database
	 * 
	 * @param stationId
	 * @param stationProvider
	 */
	public void deleteStation(String stationId, String stationProvider) {
		String where = ELEMENT_TO_STATION_STATION_ID + "='" + stationId
				+ "' AND " + ELEMENT_TO_STATION_STATION_PROVIDER + "='"
				+ stationProvider + "'";
		databaseOpenHelper.getWritableDatabase().delete(
				ELEMENT_TO_STATION_TABLE_NAME, where, null);
		where = STATION_ID + "='" + stationId + "' AND " + STATION_PROVIDER
				+ "='" + stationProvider + "'";
		databaseOpenHelper.getWritableDatabase().delete(STATION_TABLE_NAME,
				where, null);

	}

	public Cursor getAllStations(String orderBy) {
		return databaseOpenHelper.getWritableDatabase()
				.query(true, STATION_TABLE_NAME, null, null, null, null, null,
						orderBy, null);
	}

	public Cursor getAllStations(String orderBy, CharSequence constraint) {
		String where = STATION_NAME + "LIKE '%" + constraint.toString() + "%'";
		return databaseOpenHelper.getWritableDatabase().query(true,
				STATION_TABLE_NAME, null, where, null, null, null, orderBy,
				null);
	}

	public Cursor getFavoriteStations(String orderBy) {
		String where = STATION_IS_FAVORITE + "=1";
		return databaseOpenHelper.getWritableDatabase().query(true,
				STATION_TABLE_NAME, null, where, null, null, null, orderBy,
				null);
	}

	public WeatherStation getStation(String id, String provider) {
		String where = STATION_ID + "='" + id + "' AND " + STATION_PROVIDER
				+ "='" + provider + "'";
		SQLiteDatabase db = databaseOpenHelper.getReadableDatabase();
		Cursor c = db.query(STATION_TABLE_NAME, null, where, null, null, null,
				null);
		WeatherStation station = new WeatherStation();
		c.moveToFirst();
		station.setDeliverFrom(new Date(c.getLong(c
				.getColumnIndex(STATION_DELIVERS_FROM))));
		station.setDeliverTo(new Date(c.getLong(c
				.getColumnIndex(STATION_DELIVERS_TO))));
		station.setId(id);
		Location location = new Location(provider);
		location.setLatitude(c.getDouble(c.getColumnIndex(STATION_LATITUDE)));
		location.setLatitude(c.getDouble(c.getColumnIndex(STATION_LATITUDE)));
		location.setAltitude(c.getDouble(c.getColumnIndex(STATION_ALTITUDE)));
		station.setLocation(location);
		station.setName(c.getString(c.getColumnIndex(STATION_NAME)));
		station.setProvider(provider);
		station.setRefressRate(c.getDouble(c
				.getColumnIndex(STATION_REFRESS_RATE)));
		Boolean typesIsKnown = (c.getInt(c
				.getColumnIndex(STATION_ELEMENT_IS_VALID)) == 1);
		station.setTypesIsKnown(typesIsKnown);
		station.setIsFavorite((c.getInt(c.getColumnIndex(STATION_IS_FAVORITE)) == 1));
		db.close();
		c.close();

		if (typesIsKnown) {
			station.setTypes(getStationWeatherTypes(id, provider));
		}

		return station;
	}

	public Cursor getStationsFromProvider(String provider, String[] columns) {
		String where = STATION_PROVIDER + "='" + provider + "'";
		return databaseOpenHelper.getWritableDatabase().query(true,
				STATION_TABLE_NAME, columns, where, null, null, null, null,
				null);
	}

	public List<WeatherType> getStationWeatherTypes(String stationId,
			String provider) {
		String where = ELEMENT_TO_STATION_STATION_ID + "='" + stationId
				+ "' AND " + ELEMENT_TO_STATION_STATION_PROVIDER + "='"
				+ provider + "'";
		String columns[] = new String[] { ELEMENT_TO_STATION_ELEMENT };
		Cursor c = databaseOpenHelper.getReadableDatabase().query(
				ELEMENT_TO_STATION_TABLE_NAME, columns, where, null, null,
				null, null);
		c.moveToFirst();
		Integer elementCulumn = c.getColumnIndex(ELEMENT_TO_STATION_ELEMENT);

		List<WeatherType> r = new ArrayList<WeatherType>(c.getCount());
		for (int i = 0; i < c.getCount(); i++) {
			WeatherType t = new WeatherType();
			String symbol = c.getString(elementCulumn);
			t.setCode(symbol);
			r.add(t);
			c.moveToNext();
		}
		c.close();
		close();
		return r;
	}

	public Boolean isStationInDatabase(String id) {
		Cursor cursor = databaseOpenHelper.getReadableDatabase().query(
				STATION_TABLE_NAME, new String[] { "ROWID" },
				STATION_ID + "='" + id + "'", null, null, null, null);
		Boolean returnValue = (cursor.getCount() > 0);
		cursor.close();
		return returnValue;

	}

	public void resetDb() {
		databaseOpenHelper.resetDB(databaseOpenHelper.getWritableDatabase());
	}

	public void setIsFavorite(String stationId, String stationProvider,
			Boolean isFavorite) {
		ContentValues values = new ContentValues();
		values.put(STATION_IS_FAVORITE, isFavorite);
		String where = STATION_ID + "='" + stationId + "' AND "
				+ STATION_PROVIDER + "='" + stationProvider + "'";
		databaseOpenHelper.getWritableDatabase().update(STATION_TABLE_NAME,
				values, where, null);
	}

	public void setTypeIsKnown(Boolean isTypeKnown, String stationId,
			String stationProvider) {
		String where = STATION_ID + "='" + stationId + "' AND "
				+ STATION_PROVIDER + "='" + stationProvider + "'";
		ContentValues values = new ContentValues();
		values.put(STATION_ELEMENT_IS_VALID, true);
		databaseOpenHelper.getWritableDatabase().update(STATION_TABLE_NAME,
				values, where, null);
	}

	/**
	 * Add types to station, if types is null the station will be removed!
	 * 
	 * @param types
	 *            if null or zero length, station will be removed and it throws
	 *            StationIsDeletedException
	 * @param stationId
	 * @param stationProvider
	 * @throws StationIsDeletedException
	 *             is thrown when types is null or length is zero, which will
	 *             delete station
	 */
	public void setTypesToStation(List<WeatherType> types, String stationId,
			String stationProvider) throws StationIsDeletedException {
		if (types == null || types.isEmpty()) {
			deleteStation(stationId, stationProvider);
			throw new StationIsDeletedException(
					"Station do not delivers any types and is deleted");
		}
		String where = ELEMENT_TO_STATION_STATION_ID + "='" + stationId
				+ "' AND " + ELEMENT_TO_STATION_STATION_PROVIDER + "='"
				+ stationProvider + "'";
		databaseOpenHelper.getWritableDatabase().delete(
				ELEMENT_TO_STATION_TABLE_NAME, where, null);
		for (WeatherType type : types) {
			addTypeToStation(type.getCode(), stationId, stationProvider, false);
		}
		setTypeIsKnown(true, stationId, stationProvider);
	}

	private static class WeatherDatabaseOpenHelper extends SQLiteOpenHelper {
		private static final String ELEMENT_TO_STATION_TABLE_CREATE = "CREATE TABLE "
				+ ELEMENT_TO_STATION_TABLE_NAME
				+ " (_id INTEGER PRIMARY KEY, "
				+ ELEMENT_TO_STATION_STATION_ID
				+ " TEXT, "
				+ ELEMENT_TO_STATION_STATION_PROVIDER
				+ " TEXT, "
				+ ELEMENT_TO_STATION_ELEMENT + " TEXT)";

		private static final String WEATHER_STATION_TABLE_CREATE = "CREATE TABLE "
				+ STATION_TABLE_NAME
				+ " ("
				+ "_id INTEGER PRIMARY KEY, "
				+ STATION_ID
				+ " TEXT, "
				+ STATION_NAME
				+ " TEXT, "
				+ STATION_LATITUDE
				+ " REAL, "
				+ STATION_LONGTITUDE
				+ " REAL, "
				+ STATION_ALTITUDE
				+ " REAL, "
				+ STATION_DELIVERS_FROM
				+ " INTEGER, "
				+ STATION_DELIVERS_TO
				+ " INTEGER, "
				+ STATION_REFRESS_RATE
				+ " REAL, "
				+ STATION_PROVIDER
				+ " TEXT, "
				+ STATION_IS_FAVORITE
				+ " INTEGER, "
				+ STATION_ELEMENT_IS_VALID + " INTEGER)";

		public WeatherDatabaseOpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		public WeatherDatabaseOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		/*
		 * TODO: Need a way for importing database private void importDB() { try
		 * { InputStream in = mContext.getAssets().open(
		 * "databases/weather_init.sqlite"); String dbPath = "/data/data/" +
		 * mContext.getPackageName() + "/databases/" + DATABASE_NAME;
		 * OutputStream out = new FileOutputStream(dbPath); byte[] buffer = new
		 * byte[1024]; int length; while ((length = in.read(buffer)) > 0) {
		 * out.write(buffer, 0, length); } // Close the streams out.flush();
		 * out.close(); in.close();
		 * 
		 * } catch (Exception e) { e.printStackTrace(); } }
		 */

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO: importDB();
			db.execSQL(WEATHER_STATION_TABLE_CREATE);
			db.execSQL(ELEMENT_TO_STATION_TABLE_CREATE);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			resetDB(db);
		}

		public void resetDB(SQLiteDatabase db) {
			db.execSQL("DROP TABLE IF EXISTS " + ELEMENT_TO_STATION_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + STATION_TABLE_NAME);

			onCreate(db);
		}
	}

}
